<?php

class BlogPostTable extends Doctrine_Table
{
  /**
   * Returns the Doctrine_Query object that fetches published BlogPost objects.
   *
   * 
   * @param string $lang The user's culture
   * @return Doctrine_Query
   */
  public function getPublishedPostsQuery($lang = null)
  {
    $q = $this->
      createQuery('a')->
      leftJoin('a.Author')->
      leftJoin('a.Categories c')->
      leftJoin('c.Translation ct')->
      where('a.is_published = ?', true)->
      addWhere('a.published_at IS NULL OR a.published_at <= NOW()')->
      orderBy('a.published_at DESC')
    ;

    if ($lang)
    {
      $q->addWhere('a.lang = ?', $lang);
    }

    return $q;
  }

  /**
   * Returns the Doctrine_Query object that fetches published BlogPost objects 
   * for a dedicated query slug.
   *
   * 
   * @param string $lang The user's culture
   * @param string $category The category's slug
   * @return Doctrine_Query
   */
  public function getPublishedPostsByCategory($lang, $category)
  {
    return $this->getPublishedPostsQuery($lang)->addWhere('ct.slug = ?', $category);
  }

  /**
   * Returns the Doctrine_Query object that fetches published BlogPost objects 
   * matching some keywords.
   *
   * 
   * @param string $lang The user's culture
   * @param string $keywords Some keywords
   * @return Doctrine_Query
   */
  public function getPublishedPostsByKeywords($lang, $keywords)
  {
    $keywords = str_replace(',', ' ', $keywords);
    $keywords = preg_replace('/[[:space:]]{2,}/', ' ', $keywords);
    $keywords = explode(' ', trim($keywords));
    $keywords = array_map('trim', $keywords);
    $keywords = array_unique($keywords);

    $q = $this->getPublishedPostsQuery($lang);
    
    if (!count($keywords))
    {
      return $q;
    }
    
    $this->addKeywordPart($q, $keywords, 'title', true);
    $this->addKeywordPart($q, $keywords, 'source_excerpt');
    $this->addKeywordPart($q, $keywords, 'source_content');
    
    return $q;
  }

  /**
   * Retrieves an active post from its slug.
   *
   * @param array $parameters Routing parameters
   * @return BlogPost
   */
  public function retrieveActivePost(array $parameters)
  {
    return $this->getPublishedPostsQuery()->
      addWhere('a.slug = ?', $parameters['slug'])->
      fetchOne()
    ;
  }
  
  public function retrieveBackendList(Doctrine_Query $q)
  {
    $rootAlias = $q->getRootAlias();

    $q->
      addSelect('COUNT(c.id) as comments_count')->
      leftJoin($rootAlias.'.Categories c')->
      leftJoin($rootAlias.'.c.Translation t')
    ;
    
    return $q;
  }

  /**
   * Adds keywords to the given query.
   *
   * 
   * @param Doctrine_Query $q A Doctrine_Query object
   * @param array $keywords An array of keywords
   * @param string $field The field on which to apply the criteria
   * @param boolean $andClause Whether or not to add an AND clause
   * @return Doctrine_Query
   */
  protected function addKeywordPart(Doctrine_Query $q, array $keywords, $field, $andClause = false)
  {
    $query = '';
    $sep = '';
    $params = array();
    foreach ($keywords as $keyword)
    {
      $query.= sprintf('%s%s.%s LIKE ?', $sep, $q->getRootAlias(), $field);
      $params[] = '%'. $keyword .'%';
      $sep = ' OR ';
    }

    if ($andClause)
    {
      $q->andWhere($query, $params);
    }
    else
    {
      $q->orWhere($query, $params);
    }
    
    return $q;
  }
}